package com.flca.frw.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * Helper class to read excel files.
 * This is identical to ExcelHelper but with littlebit more functionality
 * @author rbakkerus
 *
 */
public class ExcelHelper {

	private String xlsName;
	private Class<?> resourceClass;
	
	private int sheetNr = 0;
	private int startRow = 0;
	private int startCol = 0;
	private int endRow = -1;
	private int endCol = -1;
	
	public ExcelHelper(String aFileName) 
	{
		xlsName = aFileName;
	}
	
	public ExcelHelper(Class<?> aRefclass, String aResourceName)
	{
		xlsName = aResourceName;
		resourceClass = aRefclass;
	}
	
	public String[][] read() throws IOException, BiffException 
	{
		InputStream is = openInputStream();
		
		return readExcelFile(is);
	}

	public String[][] read(int aSheetnr, int aStartRow, int aEndRow, int aStartCol, int aEndCol) throws IOException, BiffException 
	{
		sheetNr = aSheetnr;
		startRow = aStartRow;
		endRow = aEndRow;
		startCol = aStartCol;
		endCol = aEndCol;
		
		InputStream is = openInputStream();
		
		return readExcelFile(is);
	}

	private InputStream openInputStream() 
	{
		try {
			if (resourceClass == null) {
				return new FileInputStream(xlsName);
			} else {
				InputStream result = resourceClass.getResourceAsStream(xlsName);
				if (result != null) {
					return result;
				} else {
					throw new Exception("error opening " + xlsName);
				}
			}
		} catch (Exception e) {
			System.out.println("Error opening file " + xlsName);
			throw new RuntimeException(e);
		}
	}


	private String[][] readExcelFile(InputStream is) throws IOException, BiffException
	{
		Workbook workbook = null;
		try {
			workbook = Workbook.getWorkbook(is); 		
			
			Sheet sheet = workbook.getSheet(sheetNr); 
			
			int colCount = sheet.getColumns();
			int rowCount = sheet.getRows();
			
			if (endRow > 0 && endRow < rowCount) {
				rowCount = endRow;
			}
			if (endCol > 0 && endCol < colCount) {
				colCount = endCol;
			}
			
			String result[][] = new String[rowCount][colCount];
			
			int rIdx = 0; 
			for (int r = startRow; r < rowCount; r++) {
				int cIdx = 0;
				for (int c = startCol; c < colCount; c++) {
					Cell cell = sheet.getCell(c, r); 
//					System.out.println("" + c + " " + r + " = " + cell.getContents());
					result[rIdx][cIdx++] = cell.getContents();
				}
				rIdx++;
			}
			
			return result;
		} finally {
			workbook.close();
			is.close();
		}
	}
	
	public int getSheetNr()
	{
		return sheetNr;
	}

	public void setSheetNr(int sheet)
	{
		this.sheetNr = sheet;
	}

	public int getStartRow()
	{
		return startRow;
	}

	public void setStartRow(int startRow)
	{
		this.startRow = startRow;
	}

	public int getStartCol()
	{
		return startCol;
	}

	public void setStartCol(int startCol)
	{
		this.startCol = startCol;
	}

	public int getEndRow()
	{
		return endRow;
	}

	public void setEndRow(int endRow)
	{
		this.endRow = endRow;
	}

	public int getEndCol()
	{
		return endCol;
	}

	public void setEndCol(int endCol)
	{
		this.endCol = endCol;
	}
	
	//---------------- static methods
	
	public static String[][] readExcelFile(Class<?> aClass, String aResourceName) throws IOException, BiffException 
	{
		ExcelHelper helper = new ExcelHelper(aClass, aResourceName);
		return helper.read();
	}
	
	public static void makeExcelFile(String aFname, List<String> aHeaders) throws IOException, WriteException 
	{
		WritableWorkbook workbook = Workbook.createWorkbook(new File(aFname)); 
		WritableSheet sheet = workbook.createSheet("data", 0); 

		int c = 0;
		for (String hdr : aHeaders) {
			Label label = new Label(c, 0, hdr);
			sheet.addCell(label);
			c++;
		}

		workbook.write();
		workbook.close(); 		
	}

	
	
}